﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using clEntidad;

namespace clBD
{
    public class BDRegistro:BDBase 
    {
        public BDRegistro(OleDbConnection cn):base(cn){ }
        public BDRegistro(OleDbConnection cn, OleDbTransaction trx) : base(cn, trx) { }
        public void grabarCab(Registro elemento, int idUser, int idLogin)
        {
            elemento.id = getSeq("SEQ_REGISTRO");
            string visita = "F";
            if (elemento.persona.esVisita)
                visita = "T";
            string insert = "insert into registro_cab " +
                " (id,num_doc,ape_pat,ape_mat,nombres,empresa,cargo,fecha, visitado,ubicacion,area,id_loc,id_user,pase,visita, id_login_in)" +
                " values(" + elemento.id + ",'" + elemento.persona.numDoc.Trim().ToUpper() + "','"
                + elemento.persona.apePat.Trim().ToUpper() + "','"
                + elemento.persona.apeMat.Trim().ToUpper() + "','"
                + elemento.persona.nombres.Trim().ToUpper() + "','"
                + elemento.persona.empresa.Trim().ToUpper() + "','"
                + elemento.persona.cargo.Trim().ToUpper() + "',now,'"
                + elemento.persona.visitado.nombre + "','"
                + elemento.persona.visitado.ubicacion + "','"
                + elemento.persona.visitado.area + "',"
                + elemento.idLocal.ToString() + "," 
                + idUser + ",'" + elemento.NumeroPase + "','" + visita +"'," + idLogin + ")";
                
            execute(insert);
        }
        public void guardarImagen(int id,byte[] abImagen)
        {
            OleDbCommand comm = new OleDbCommand("INSERT INTO foto (id,foto) VALUES (?,?)", this.cn);

            OleDbParameter parId = new OleDbParameter("@id", OleDbType.BigInt );
            parId.Value = id;
            comm.Parameters.Add(parId);

            OleDbParameter parImagen = new OleDbParameter("@imagen", OleDbType.VarBinary, abImagen.Length);
            parImagen.Value = abImagen;
            comm.Parameters.Add(parImagen);

            comm.ExecuteNonQuery();
        }
        public int buscarIngreso(int id, int idLocal)
        {
            int idRpta = -1;
            string query = "select id from registro_cab where id=" + id.ToString()
                + " and id_loc=" + idLocal.ToString() + " and salida is null ";
            OleDbDataReader dr = execDr(query);

            if (dr.Read())
            {
                idRpta=id ;
            }
            return idRpta;
        }
        public int buscarIngreso(String numDoc, int idLocal)
        {
            int id = -1;
            string query = "select id from registro_cab where num_doc='" + numDoc 
                + "' and id_loc=" + idLocal.ToString() + " and salida is null " 
                + " order by id desc";
            OleDbDataReader dr = execDr(query);

            if (dr.Read())
            {
                id = dr.GetInt32(0);
            }
            return id;
        }
        public int buscarIngresoEquipo(String numSerie, int idLocal)
        {
            int id = -1;
            string query = "select id from registro_cab a where exists (select 1 from registro_det d where num_serie='" 
                + numSerie + "' and a.id=d.id)"
                + " and id_loc=" + idLocal.ToString() + " and salida is null "
                + " order by id desc";
            OleDbDataReader dr = execDr(query);

            if (dr.Read())
            {
                id = dr.GetInt32(0);
            }
            return id;
        }
        public Registro getCab(int id)
        {
            Registro elem = null;
            String query = " select id,fecha,num_doc, ape_pat,ape_mat,nombres,empresa,cargo,salida, " +
                " visitado,ubicacion,area,id_loc " +
                " from registro_cab where id=?";
            OleDbCommand comm = new OleDbCommand(query , this.cn);
            comm.Parameters.Add(new OleDbParameter("ID", OleDbType.Integer, 0, "ID"));
            comm.Parameters["ID"].Value = id;
            OleDbDataReader dr = comm.ExecuteReader();
            if (dr.Read())
            {
                elem = new Registro();
                elem.id = id;

                elem.entrada = dr.GetDateTime(1);

                elem.persona.numDoc = dr.GetString(2);
                elem.persona.apePat = dr.GetString(3);
                if (!dr.IsDBNull(4))
                    elem.persona.apeMat = dr.GetString(4);
                elem.persona.nombres = dr.GetString(5);

                elem.persona.empresa = dr.GetString(6);
                elem.persona.cargo = dr.GetString(7);
                if (!dr.IsDBNull(8))
                {
                    elem.salio = true;
                    elem.salida = dr.GetDateTime(8);
                }
                if (!dr.IsDBNull(9))
                    elem.persona.visitado.nombre = dr.GetString(9);
                if (!dr.IsDBNull(10))
                    elem.persona.visitado.ubicacion = dr.GetString(10);
                if (!dr.IsDBNull(11))
                    elem.persona.visitado.area = dr.GetString(11);
                if (!dr.IsDBNull(12))
                    elem.idLocal = dr.GetInt32(12);

            }
            return elem;
        }

        public OleDbDataReader getDet(int id)
        {
            String query = " select num_serie,tipo,marca,modelo " +
                " from registro_det where id=?";
            OleDbCommand comm = new OleDbCommand(query, this.cn);
            comm.Parameters.Add(new OleDbParameter("ID", OleDbType.Integer, 0, "ID"));
            comm.Parameters["ID"].Value = id;
            return comm.ExecuteReader();
        }
        public byte[] getFoto(int id)
        {
            Registro elem = new Registro();
            OleDbCommand comm = new OleDbCommand("SELECT foto FROM foto where ID=?", this.cn);
            comm.Parameters.Add(new OleDbParameter("ID", OleDbType.Integer, 0, "ID"));
            comm.Parameters["ID"].Value = id ;
            OleDbDataReader dr = comm.ExecuteReader();
            byte[] aBytes = null;
            if (dr.Read())
            {
                aBytes = (byte[])dr.GetValue(0);
            }
            return aBytes;
        }
        public List<Registro> getByFec(DateTime fecha, int idLocal)
        {
            List<Registro> elems = new List<Registro>();
            DateTime fecFin = fecha.AddDays(1);
            String query = "SELECT id,fecha,num_doc, ape_pat,ape_mat,nombres,empresa,cargo,salida,visita"+
                " FROM " 
                + "REGISTRO_CAB where FECHA>=? and fecha<? and id_loc=? order by id";
            OleDbCommand comm = new OleDbCommand(query, this.cn);

            comm.Parameters.Add(new OleDbParameter("fecha", OleDbType.Date, 0, "fecha"));
            comm.Parameters["fecha"].Value = fecha;

            comm.Parameters.Add(new OleDbParameter("fecFin", OleDbType.Date, 0, "fecFin"));
            comm.Parameters["fecFin"].Value = fecFin;

            comm.Parameters.Add(new OleDbParameter("id_loc", OleDbType.Numeric, 0, "id_loc"));
            comm.Parameters["id_loc"].Value = idLocal;

            OleDbDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                Registro elem = new Registro();
                elem.id = dr.GetInt32(0);
                elem.entrada = dr.GetDateTime(1);

                elem.persona.numDoc = dr.GetString(2);
                elem.persona.apePat = dr.GetString(3);
                elem.persona.apeMat = dr.GetString(4);
                elem.persona.nombres  = dr.GetString(5);

                elem.persona.empresa = dr.GetString(6);
                if (!dr.IsDBNull(8))
                {
                    elem.salio = true;
                    elem.salida = dr.GetDateTime(8);
                }
                if (dr.GetString(9).Equals("T"))
                    elem.esVisita = true;
                elems.Add(elem);
            }
            return elems;

        }

        public void grabarDet(Equipo elemento, int id)
        {
            string insert = "insert into registro_det " +
                " (id,num_serie,tipo,marca,modelo)" +
                " values(" + id + ",'"
                + elemento.numSerie.Trim().ToUpper() + "','"
                + elemento.tipo.Trim().ToUpper() + "','"
                + elemento.marca.Trim().ToUpper() + "','"
                + elemento.modelo.Trim().ToUpper() + "')";

            execute(insert);
        }
        public void salio(int id, int idLogin)
        {
            String update = "update registro_cab set salida=now,id_login_out=? where id=?";

            OleDbCommand comm = new OleDbCommand(update, this.cn);

            OleDbParameter parIdLogin = new OleDbParameter("@id_login_out", OleDbType.BigInt);
            parIdLogin.Value = idLogin;
            comm.Parameters.Add(parIdLogin);

            OleDbParameter parId = new OleDbParameter("@id", OleDbType.BigInt);
            parId.Value = id;
            comm.Parameters.Add(parId);

            comm.ExecuteNonQuery();
        }
    }
}
